USE Travian
/*
Name:  Insert_Command
Description:  Insert into Commands table.
Author : Daneo van Overloop
13-1-2012    Daneo van Overloop
*/
IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Command_Insert'
              AND TYPE = 'p')
DROP PROCEDURE Command_Insert
GO
CREATE PROCEDURE Command_Insert
@Command_Name VARCHAR(12),
@Access_lvl integer
AS
INSERT INTO commands
VALUES (@Command_Name,@Access_lvl);
GO

IF EXISTS (
			SELECT *
			FROM Travian.dbo.sysobjects
			WHERE Name = 'Command_Delete'
			AND TYPE = 'p')
DROP PROCEDURE Command_Delete
GO
CREATE PROCEDURE Command_Delete
@Cmd_Name VARCHAR(12)
AS
DELETE FROM commands
WHERE Command_Name = @Cmd_Name
GO

/*
Name:  Get_Members
Description:  Get Alliance members from data.
Author : Daneo van Overloop
16-1-2012    Daneo van Overloop
*/
IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Members'
              AND TYPE = 'p')
DROP PROCEDURE Get_Members
GO
CREATE PROCEDURE Get_Members
@Alliance VARCHAR(12)
AS 
Select * from Players x where (alliance = @Alliance);
GO

/*
Name:  Get_units
Description:  Get Units per race
Arguments : Race = Race from which to get units.
Author : Daneo van Overloop
16-1-2012    Daneo van Overloop
*/
IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Units'
              AND TYPE = 'p')
DROP PROCEDURE Get_Units
GO
CREATE PROCEDURE Get_Units
@Race Varchar(11)
AS
Select u.Unit_id,u.Unit_Name,u.Unit_Speed,u.Unit_Race,u.Loot,u.Funct,u.Type FROM units u WHERE (u.Unit_Race = @Race);
GO

/*
Name:  Get_race
Description:  Get Races
Arguments : tid = Tribe ID.
Author : Daneo van Overloop
6-2-2012    Daneo van Overloop
*/
IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Races'
              AND TYPE = 'p')
DROP PROCEDURE Get_Races
GO
CREATE PROCEDURE Get_Races
AS 
SELECT * FROM races;
GO
/*
Name:  Get_ally_stats
Description:  Get global alliance stats
Arguments : Race = Race from which to get units.
Author : Daneo van Overloop
16-1-2012    Daneo van Overloop
*/
IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Ally_Stats'
              AND TYPE = 'p')
DROP PROCEDURE Get_Ally_Stats
GO
CREATE PROCEDURE Get_Ally_Stats
@Ally varchar(20)
AS
Select a.* FROM Alliance_Stats a WHERE a.Name = @Ally  ORDER BY 'TOP' DESC;
GO

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Villages'
              AND TYPE = 'p')
DROP PROCEDURE Get_Villages
GO
CREATE PROCEDURE Get_Villages
@Player varchar(20)
AS
SELECT x.Vid AS VID,x.village as Name,x.distance_id as Distance ,x.x as X,x.y as Y,x.player as Player FROM x_world x WHERE x.player = @Player ORDER BY x.village Asc
GO

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_StoredProcedures'
              AND TYPE = 'p')
DROP PROCEDURE Get_StoredProcedures
GO
CREATE PROCEDURE Get_StoredProcedures
AS
SELECT * FROM Travian.dbo.sysobjects 
WHERE type = 'p' and category = 0
GO

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Views'
              AND TYPE = 'p')
DROP PROCEDURE Get_Views
GO
CREATE PROCEDURE Get_Views
AS
SELECT * FROM Travian.dbo.sysobjects 
WHERE type = 'v' and category = 0
GO

IF EXISTS (
           SELECT *
             FROM Travian.dbo.sysobjects
            WHERE Name = 'Get_Member'
              AND TYPE = 'p')
DROP PROCEDURE Get_Member
GO
CREATE PROCEDURE Get_Member
@Member varchar(20)
AS
SELECT * FROM Players p WHERE p.name = @Member
GO

IF EXISTS(
			SELECT *
			FROM Travian.dbo.sysobjects
			WHERE Name = 'Populate_Members'
			AND TYPE = 'p')
DROP PROCEDURE Populate_Members
GO
CREATE PROCEDURE Populate_Members
@Alliance VARCHAR(20)
AS
IF (SELECT priv.Access_lvl FROM priveleged_members priv, players p WHERE priv.PID = p.PID) > 3
BEGIN
INSERT INTO members(Member_Name, Race_ID, Access_lvl)
SELECT p.Name, p.Tribe, priv.Access_lvl FROM players p, priveleged_members priv WHERE priv.PID = p.PID
END
ELSE
BEGIN
INSERT INTO members(Member_Name, Race_ID)
SELECT p.Name, p.Tribe FROM players p WHERE p.Alliance = @Alliance
END
GO

IF EXISTS(
			SELECT *
			FROM Travian.dbo.sysobjects
			WHERE Name = 'Update_Priveleged_Member'
			AND TYPE = 'p')
DROP PROCEDURE Update_Priveleged_Member
GO
CREATE PROCEDURE Update_Priveleged_Member
@Access_lvl integer,
@Name VARCHAR(20)
AS
UPDATE priveleged_members
SET Access_lvl = @Access_lvl
WHERE PID = (SELECT p.PID FROM players p where p.name = @Name)
GO

IF EXISTS(
			SELECT *
			FROM Travian.dbo.sysobjects
			WHERE Name = 'Web_CheckIfUserExists'
			AND TYPE = 'p')
DROP PROCEDURE Web_CheckIfUserExists
GO
CREATE PROCEDURE Web_CheckIfUserExists
@UserName VARCHAR(20)
AS
SELECT * FROM website_users w WHERE w.WebUserName = @Username
GO

IF EXISTS(
			SELECT *
			FROM Travian.dbo.sysobjects
			WHERE Name = 'Web_GetPassword'
			AND TYPE = 'p')
DROP PROCEDURE Web_GetPassword
GO
CREATE PROCEDURE Web_GetPassword
@Username VARCHAR(20)
AS
SELECT w.Web_Password FROM website_users w WHERE w.WebUserName = @Username
GO

IF EXISTS(
			SELECT *
			FROM Travian.dbo.sysobjects
			WHERE Name = 'Web_RegisterUser'
			AND TYPE = 'p')
DROP PROCEDURE Web_RegisterUser
GO
CREATE PROCEDURE Web_RegisterUser
@UserName VARCHAR(20),
@Password VARCHAR(20),
@Email VARCHAR(50),
@FullName VARCHAR(50),
@Country VARCHAR(20)
AS
INSERT INTO website_users(WebUserName, Web_Password, Web_Email, Web_FullName, Web_Country) 
VALUES(@UserName, @Password, @Email, @FullName, @Country)
GO

IF EXISTS( 
               SELECT * 
               FROM Travian.dbo.sysobjects 
               WHERE Name = 'Get_Commands' 
               AND TYPE = 'p') 
DROP PROCEDURE Get_Commands 
GO 
CREATE PROCEDURE Get_Commands 
@Accesslvl int 
As 
Select c.* from commands c where c.Access_lvl <= @Accesslvl; 
GO 

IF EXISTS( 
               SELECT * 
               FROM Travian.dbo.sysobjects 
               WHERE Name = 'Get_Allies' 
               AND TYPE = 'p') 
DROP PROCEDURE Get_Allies
GO 
CREATE PROCEDURE Get_Allies
AS
Select DISTINCT x.aid,x.alliance from x_world x;
GO
